# -*- coding: utf8 -*-
import logging
import pymysql
import pymysql.cursors

from scrapy.utils.project import get_project_settings

settings = get_project_settings()
dbconf = settings['MYSQL_DB']

class _MySQL(object):
    def __init__(self, host, port, user, passwd, db, charset='utf8'):
        self.conn = pymysql.connect(
            host = host,
            port = port,
            user = user,
            passwd = passwd,
            db = db,
            charset = charset,
            cursorclass = pymysql.cursors.DictCursor
        )

    def get_cursor(self):
        return self.conn.cursor()

    def query(self, sql):
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, None)
            result = cursor.fetchall()
        except Exception as e:
            logging.error("mysql query error: %s", e)
            return None
        finally:
            cursor.close()
        return result

    def execute(self, sql, param=None):
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, param)
            self.conn.commit()
            affected_row = cursor.rowcount
        except Exception as e:
            logging.error("mysql execute error: %s", e)
            return 0
        finally:
            cursor.close()
        return affected_row

    def executemany(self, sql, params=None):
        cursor = self.get_cursor()
        try:
            cursor.executemany(sql, params)
            self.conn.commit()
            affected_rows = cursor.rowcount
        except Exception as e:
            logging.error("mysql executemany error: %s", e)
            return 0
        finally:
            cursor.close()
        return affected_rows

    def close(self):
        try:
            self.conn.close()
        except:
            pass

    def __del__(self):
        self.close()

mysql = _MySQL(
    host=dbconf['host'],
    user=dbconf['user'],
    passwd=dbconf['password'],
    db=dbconf['name'],
    port=dbconf['port'],
    charset=dbconf['charset'],
)

def create_table():
    table = """
            CREATE TABLE IF NOT EXISTS `watchdog`(
                `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `name` varchar(100),
                `price` int(11) NOT NULL DEFAULT 0
            ) ENGINE=InnoDB charset=utf8;
            """
    print(mysql.execute(table))

def insert_data():
    params = [('dog_%d' % i, i) for i in range(12)]
    sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"
    print(mysql.executemany(sql, params))

def select_data():
    query = "select `id`,`name`,`price` from `watchdog`;";
    li = mysql.query(query)
    print(li)

if __name__ == '__main__':
    # create_table()
    # insert_data()
    # select_data()
    pass
